from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, SmallInteger, DATETIME, Time, Date, create_engine
from sqlalchemy import desc, and_, or_

"""
    考勤分析
"""

DATABASE_URI = 'mysql://root:xxx@xxx:3306/xxx?charset=utf8mb4'

engine = create_engine(DATABASE_URI)

Base = declarative_base()

Session = sessionmaker(bind=engine)
# 判断当前是否是工作日，如果是则查询是否有当天的数据，没有则生成，有则更新
# 生成数据
"""
    1. 查询全部学生的信息：当天日期、学生姓名、学生登录名、性质、年级、班级
    2. 查询学生进出数据：
        进校时间（当天最早进校时间）
        离校时间（当天最迟离校时间）
        是否迟到
        位置
    3. 查询学生请假数据：
        请假开始时间	请假结束时间	请假状态
"""
from datetime import datetime, date, time, timedelta

# 判断当前是否是工作日，如果是则查询是否有当天的数据，没有则生成，有则更新
# 生成数据
"""
    1. 查询全部学生的信息：当天日期、学生姓名、学生登录名、性质、年级、班级
    2. 查询学生进出数据：
        进校时间（当天最早进校时间）
        离校时间（当天最迟离校时间）
        是否迟到
        位置
    3. 查询学生请假数据：
        请假开始时间	请假结束时间	请假状态
"""


# 学生信息
class StatusBasic(Base):
    __tablename__ = 'jwxtnew_stud_personal_status_basic'
    id = Column(String, primary_key=True)
    profile_id = Column(String)
    stud_nature = Column(String)

    def __repr__(self):
        return f"<StatusBasic(profile_id={self.profile_id}" \
               f", stud_nature='{self.stud_nature}')>"


def queryStatusBasic(**kwargs):
    session = Session()
    query = session.query(StatusBasic)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(StatusBasic, key):
            query = query.filter(getattr(StatusBasic, key) == value)

    # 执行查询并返回结果
    basics = query.first()
    session.close()
    return basics


# 学生考勤
class StudentAttendance(Base):
    __tablename__ = 'sjkf_t_student_kqfx'
    id = Column(String, primary_key=True)
    today = Column(Date)
    entryTime = Column(Time)
    departureTime = Column(Time)
    studentGrade = Column(String)
    studentClass = Column(String)
    realName = Column(String)
    loginName = Column(String)
    nature = Column(String)
    isLate = Column(SmallInteger)
    entryLocation = Column(String)
    departureLocation = Column(String)
    leaveStartTime = Column(DATETIME)
    leaveEndTime = Column(DATETIME)
    leaveStatus = Column(SmallInteger)

    def __repr__(self):
        return f"<StudentAttendance(today={self.today}, realName='{self.realName}', loginName='{self.loginName}')>"


def queryStudentAttendance(**kwargs):
    session = Session()
    query = session.query(StudentAttendance)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(StudentAttendance, key):
            query = query.filter(getattr(StudentAttendance, key) == value)

    # 执行查询并返回结果
    students = query.all()
    session.close()
    return students


def is_workday(date):
    """
    判断给定的日期是否是工作日（周一至周五），同时排除公共假期。

    :param date: datetime.date 或 datetime.datetime 对象
    :return: True 如果是工作日且非公共假期，否则 False
    """
    return date.weekday() < 5


in_type = 1
out_type = 2


# 进出记录表
class AccessRecord(Base):
    __tablename__ = 'ssgl_v_term_student_accessrecord'
    id = Column(Integer, primary_key=True)
    student_code = Column(String)
    student_id = Column(String)
    student_name = Column(String)
    create_time = Column(DATETIME)
    number = Column(String)
    type = Column(Integer)
    gate_code = Column(String)
    student_loginname = Column(String)
    operator = Column(String)

    def __repr__(self):
        return f"<AccessRecord(id={self.id}" \
               f", student_code='{self.student_code}" \
               f", gate_code='{self.gate_code}')>"


def queryAccessRecord(student_loginname, gate_code, type, cur_time):
    session = Session()
    query = session.query(AccessRecord)  # 创建今天的最开始时间点
    today_start = datetime.combine(cur_time, time.min)
    # 创建今天的最晚时间点
    today_end = datetime.combine(cur_time, time.max)
    # 添加条件
    query = query.filter(
        AccessRecord.student_loginname == student_loginname,
        AccessRecord.gate_code.like(gate_code),
        AccessRecord.type == type,
        and_(AccessRecord.create_time > today_start, AccessRecord.create_time < today_end)
    )
    # 执行查询并返回结果
    record = query.order_by(desc(AccessRecord.create_time)).first()
    session.close()
    return record


def chooseOneRecord(records, type):
    if len(records) == 0:
        return
    if type == 'early':
        createtime = records[0].create_time
        result = records[0]
        for record in records:
            if record.create_time < createtime:
                result = record
        return result
    elif type == 'late':
        createtime = records[0].create_time
        result = records[0]
        for record in records:
            if record.create_time > createtime:
                result = record
        return result


class Zxs(Base):
    __tablename__ = 'ssgl_v_sjfx_zxxsxx'
    LOGIN_NAME = Column(String, primary_key=True)
    room_name = Column(String)

    def __repr__(self):
        return f"<Zxs(LOGIN_NAME={self.LOGIN_NAME}, room_name='{self.room_name}')>"


def isZxs(**kwargs):
    session = Session()
    query = session.query(Zxs)
    # 添加条件
    for key, value in kwargs.items():
        if hasattr(Zxs, key):
            query = query.filter(getattr(Zxs, key) == value)

    # 执行查询并返回结果
    zxs = query.first()
    session.close()
    if zxs:
        if zxs.room_name is not None:
            return True
        else:
            return False
    else:
        return False


def query_in_record(loginName, cur_time):
    data = []
    if isZxs(LOGIN_NAME=loginName):
        # 住校生，出宿舍和进南北门、地下室都表示进入校园
        in_ss = queryAccessRecord(student_loginname=loginName, gate_code='%宿舍%', type=out_type, cur_time=cur_time)
        if in_ss is not None:
            data.append(in_ss)
    else:
        # 非住校生，进南北门、地下室都表示进入校园
        pass
    in_nm = queryAccessRecord(student_loginname=loginName, gate_code='%南门%', type=in_type, cur_time=cur_time)
    in_bm = queryAccessRecord(student_loginname=loginName, gate_code='%北门%', type=in_type, cur_time=cur_time)
    in_dxs = queryAccessRecord(student_loginname=loginName, gate_code='%地下室%', type=in_type, cur_time=cur_time)
    if in_nm is not None:
        data.append(in_nm)
    if in_bm is not None:
        data.append(in_bm)
    if in_dxs is not None:
        data.append(in_dxs)
    record = chooseOneRecord(data, 'early')
    return record


def query_out_record(loginName, cur_time):
    data = []
    if isZxs(LOGIN_NAME=loginName):
        # 住校生，进宿舍和出南北门、地下室都表示出校园。
        out_ss = queryAccessRecord(student_loginname=loginName, gate_code='%宿舍%', type=in_type, cur_time=cur_time)
        if out_ss is not None:
            data.append(out_ss)
    else:
        # 非住校生，出南北门、地下室都表示出校园
        pass
    out_nm = queryAccessRecord(student_loginname=loginName, gate_code='%南门%', type=out_type, cur_time=cur_time)
    out_bm = queryAccessRecord(student_loginname=loginName, gate_code='%北门%', type=out_type, cur_time=cur_time)
    out_dxs = queryAccessRecord(student_loginname=loginName, gate_code='%地下室%', type=out_type, cur_time=cur_time)
    if out_nm is not None:
        data.append(out_nm)
    if out_bm is not None:
        data.append(out_bm)
    if out_dxs is not None:
        data.append(out_dxs)
    record = chooseOneRecord(data, 'late')
    return record


# 学生表
class Student(Base):
    __tablename__ = 'jwxtnew_v_nbxc_student'
    LOGIN_NAME = Column(String, primary_key=True)
    grade_name = Column(String)
    name = Column(String)
    student_name = Column(String)
    student_id = Column(String)

    def __repr__(self):
        return f"<Student(LOGIN_NAME={self.LOGIN_NAME}" \
               f", student_name='{self.student_name}')>"


def queryStudent():
    session = Session()
    students = session.query(Student).all()

    # for student in students:
    #     print(student)

    session.close()
    return students


# 请假表
class Leave(Base):
    __tablename__ = 'ssgl_v_student_leave'
    LOGIN_NAME = Column(String, primary_key=True)
    start_time = Column(DATETIME)
    end_time = Column(DATETIME)
    back = Column(SmallInteger)

    def __repr__(self):
        return f"<Leave(LOGIN_NAME={self.LOGIN_NAME}" \
               f", start_time='{self.start_time}'" \
               f", end_time='{self.end_time}'" \
               f", back='{self.back}'" \
               f")>"


# 临时请假
def queryLeave(loginName, cur_time):
    today_start = datetime.combine(cur_time, time.min)
    today_end = datetime.combine(cur_time, time.max)
    session = Session()
    query = session.query(Leave)
    query = query.filter(or_(
        and_(
            Leave.LOGIN_NAME == loginName
            , Leave.start_time.isnot(None)
            , Leave.end_time.isnot(None)
            , Leave.start_time > today_start
            , Leave.end_time < today_end
        )
    ))
    leaves = query.first()
    session.close()
    return leaves


# 请假表
class Qjsq(Base):
    __tablename__ = 'ywtb103_t_nfls_xswcqjsq'
    SQRGH = Column(String, primary_key=True)
    QJKSSJ = Column(String)
    QJJSSJ = Column(String)
    SFXJ = Column(String)

    def __repr__(self):
        return f"<Qjsq(SQRGH={self.SQRGH}" \
               f", QJKSSJ='{self.QJKSSJ}'" \
               f", QJJSSJ='{self.QJJSSJ}'" \
               f", SFXJ='{self.SFXJ}'" \
               f")>"


# 长期请假
def queryQjsq(loginName, cur_time):
    today = cur_time
    today_start = datetime.combine(cur_time, time.min)
    today_end = datetime.combine(cur_time, time.max)
    session = Session()
    query = session.query(Qjsq)
    query = query.filter(or_(
        and_(
            Qjsq.SQRGH == loginName
            , Qjsq.QJKSSJ.isnot(None)
            , Qjsq.QJJSSJ.isnot(None)
            , Qjsq.QJKSSJ < today_start
            , Qjsq.QJJSSJ > today_end
        ),
        and_(
            Qjsq.SQRGH == loginName
            , Qjsq.QJJSSJ.is_(None)
            , Qjsq.QJKSSJ < today_start
            , Qjsq.SFXJ.is_(None)
        )
    ))
    qjsqs = query.first()
    session.close()
    return qjsqs


def generate_data(now_time):
    students = queryStudent()
    studentAttendances = []
    for student in students:
        studentAttendance = {}
        basic = queryStatusBasic(profile_id=student.student_id)
        in_accessRecord = query_in_record(loginName=student.LOGIN_NAME, cur_time=now_time)
        out_accessRecord = query_out_record(loginName=student.LOGIN_NAME, cur_time=now_time)

        studentAttendance['today'] = now_time
        if basic:
            studentAttendance['nature'] = basic.stud_nature
        else:
            studentAttendance['nature'] = ''
        studentAttendance['studentGrade'] = student.grade_name
        studentAttendance['studentClass'] = student.name
        studentAttendance['realName'] = student.student_name
        studentAttendance['loginName'] = student.LOGIN_NAME
        if in_accessRecord:
            time_only = in_accessRecord.create_time.strftime("%H:%M:%S")
            studentAttendance['entryTime'] = time_only
            studentAttendance['entryLocation'] = in_accessRecord.gate_code
            time_7_40 = time(hour=7, minute=40)
            entry_time = datetime.strptime(time_only, "%H:%M:%S").time()
            if entry_time > time_7_40:
                studentAttendance['isLate'] = 1
            else:
                studentAttendance['isLate'] = 0
        else:
            studentAttendance['entryTime'] = None
            studentAttendance['isLate'] = None
            studentAttendance['entryLocation'] = None

        if out_accessRecord:
            time_only = out_accessRecord.create_time.strftime("%H:%M:%S")
            studentAttendance['departureTime'] = time_only
            studentAttendance['departureLocation'] = out_accessRecord.gate_code
        else:
            studentAttendance['departureTime'] = None
            studentAttendance['departureLocation'] = None
        leave = queryLeave(student.LOGIN_NAME, now_time)
        if leave:
            studentAttendance['leaveStartTime'] = leave.start_time
            studentAttendance['leaveEndTime'] = leave.end_time
            if leave.back:
                studentAttendance['leaveStatus'] = 1
            else:
                studentAttendance['leaveStatus'] = 0
        else:
            qjsq = queryQjsq(student.LOGIN_NAME, now_time)
            if qjsq:
                studentAttendance['leaveStartTime'] = qjsq.QJKSSJ
                studentAttendance['leaveEndTime'] = qjsq.QJJSSJ
            if qjsq and qjsq.SFXJ == 1:
                studentAttendance['leaveStatus'] = 1
        studentAttendances.append(studentAttendance)
    print(f'------------generate_data----------{len(studentAttendances)}')
    return studentAttendances


def addOrUpdate(datas):
    session = Session()
    print(f"------------addOrUpdate---------{len(datas)}")
    for data in datas:
        query = session.query(StudentAttendance).filter(StudentAttendance.today == data['today'],
                                                        StudentAttendance.loginName == data['loginName'])
        record = query.first()
        if not record:
            addStudentAttendance(data)
        else:
            query.update(data, synchronize_session=False)
            session.commit()


def addStudentAttendance(data):
    session = Session()

    # 创建一个新的 Book 对象
    if 'entryTime' in data:
        entryTime = data['entryTime']
    if 'departureTime' in data:
        departureTime = data['departureTime']
    if 'isLate' in data:
        isLate = data['isLate']
    else:
        isLate = None
    if data['nature'] is None:
        nature = ''
    else:
        nature = data['nature']
    if 'entryLocation' in data:
        entryLocation = data['entryLocation']
    else:
        entryLocation = None
    if 'departureLocation' in data:
        departureLocation = data['departureLocation']
    else:
        departureLocation = None
    if 'leaveStartTime' in data:
        leaveStartTime = data['leaveStartTime']
    else:
        leaveStartTime = None
    if 'leaveEndTime' in data:
        leaveEndTime = data['leaveEndTime']
    else:
        leaveEndTime = None
    if 'leaveStatus' in data:
        leaveStatus = data['leaveStatus']
    else:
        leaveStatus = None
    studentAttendance = StudentAttendance(
        id=data['loginName'] + data['today'].strftime("%Y%m%d")
        , today=data['today']
        , entryTime=entryTime
        , departureTime=departureTime
        , studentGrade=data['studentGrade']
        , studentClass=data['studentClass']
        , realName=data['realName']
        , loginName=data['loginName']
        , nature=nature
        , isLate=isLate
        , entryLocation=entryLocation
        , departureLocation=departureLocation
        , leaveStartTime=leaveStartTime
        , leaveEndTime=leaveEndTime
        , leaveStatus=leaveStatus
    )

    session.add(studentAttendance)

    session.commit()
    session.close()


# 根据日期遍历判断有没有每天的数据
def generate_data_of_everyDay(startTime, endTime):
    start_date = datetime.strptime(startTime, "%Y-%m-%d")
    end_date = datetime.strptime(endTime, "%Y-%m-%d")
    # 初始化当前日期为开始日期
    current_date = start_date
    # 遍历从开始日期到结束日期之间的每一天
    while current_date <= end_date:
        todayData = hasTodayData(current_date)
        print(f"{current_date.date()}有数据吗？{todayData}")
        if not todayData:
            print("生成数据")
            # print(f"生成{current_date.date()}数据")
            generate_data_of_oneDay(current_date)
        # 将当前日期增加一天
        current_date += timedelta(days=1)


# 判断是否有今天的数据
def hasTodayData(today):
    students = queryStudentAttendance(today=today)
    # students = [1]
    if len(students) > 0:
        if datetime(date.today().year, date.today().month, date.today().day, 0, 0, 0) == today:
            return False;
        else:
            return True;
    else:
        return False;


def generate_data_of_oneDay(now_time):
    # now_time = date.today()
    print(f'今天的日期是{now_time}')
    if is_workday(now_time):
        print('今天是工作日')
        # if not hasTodayData(now_time):
        print(f'{now_time}的数据尚未生成')
        print('-----------生成数据开始-------------')
        kaoqin = generate_data(now_time)
        print('-----------生成数据结束-------------')
        print('-----------插入数据开始-------------')
        addOrUpdate(kaoqin)
        print('-----------插入数据结束-------------')
    else:
        print('今天不是工作日，不生成数据')


if __name__ == '__main__':
    # mainFunction()
    today = str(date.today())
    generate_data_of_everyDay('2024-08-29', today)
